package com.wolfpire.workflow.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.wolfpire.system.common.EasyuiPage;
import com.wolfpire.system.common.base.dao.impl.BaseHibernateDao;
import com.wolfpire.workflow.dao.JbpmTaskDao;
import com.wolfpire.workflow.om.JbpmTask;


@Repository
public class JbpmTaskDaoImpl extends BaseHibernateDao<JbpmTask, Long> implements JbpmTaskDao {

	@SuppressWarnings("unchecked")
	public List<JbpmTask> queryTaskListByTimeout() {

		Criteria criteria = createCriteria(Restrictions.isNull("timeout"));
		return criteria.list();

	}

	@SuppressWarnings("unchecked")
	public List<JbpmTask> queryTaskListByNotTimeout() {

		Criteria criteria = createCriteria(Restrictions.and(Restrictions.or(
				Restrictions.isNull("timeout"),
				Restrictions.eq("timeout", "1")),Restrictions.eq("timeout", "2"))
		);
		return criteria.list();

	}
	
	/**
	 * 根据任务ID获取参与者列表(参与者ID与参与者类型)
	 * @param taskId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryParticipationListByTaskId(Long taskId) {
		final String sql = "SELECT T.USERID_, T.TYPE_,T.GROUPID_ FROM  JBPM4_PARTICIPATION T WHERE T.TASK_ =?";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, taskId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	
	@SuppressWarnings("unchecked")
	public List<BigDecimal> queryTaskListByProcinst(Long procinst, Long deploymentId, String[] userIds) {
		
		StringBuffer sql = new StringBuffer("SELECT T.DBID_ FROM JBPM4_TASK T ");
		sql.append(" WHERE T.PROCINST_ =").append(procinst).append("  AND T.NAME_ NOT IN ");
		sql.append(" (SELECT T.NODE_NAME FROM WF_NODE T WHERE T.DEPLOYMENT_ID =").append(deploymentId);
		sql.append("  AND T.NODE_ID IN (");
		for (String userId : userIds) {
			String[] user = userId.split("-");
			
			sql.append(user[0]).append(",");     
			
			
				
		}
		sql.append("-1))");
		//String sql = "SELECT T.DBID_  FROM JBPM4_TASK T WHERE T.PROCINST_=?";
		Query query = getSession().createSQLQuery(sql.toString());
		//query.setParameter(0, procinst);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getJbpmTaskByTaskId(Long taskId) {
		String sql = "SELECT TO_CHAR(T.CREATE_, 'yyyy-mm-dd HH:mi:ss') AS CREATE_ FROM  JBPM4_TASK T WHERE T.DBID_ =?";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, taskId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getJbpmTaskByHistoryTaskId(Long taskId) {
		String sql = "SELECT TO_CHAR(T.END_TIME, 'yyyy-mm-dd HH:mi:ss') AS END_TIME,T.NODE_ID,T.TASK_NAME,T.INSTANCE_ID FROM WF_TASK T WHERE T.TASK_ID =?";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, taskId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getJbpmTaskIdByInstanceId(Long instanceId) {
		String sql = " SELECT T1.DBID_ AS WF_TASKID, T1.NAME_, T.INSTANCE_ID, T.USER_ID, "
			       		+ " T.USER_NAME, T.ORG_NAME, T2.USERID_, T2.TYPE_ "
			       		+ " FROM WF_PROCESS_INSTANCE T "
			       		+ " JOIN JBPM4_TASK T1 "
			       		+ " ON T.INSTANCE_ID = T1.PROCINST_ "
			       		+ " AND T1.PROCINST_ = ? "
			       		+ " LEFT JOIN JBPM4_PARTICIPATION T2 "
			       		+ " ON T2.TASK_ = T1.DBID_ ";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, instanceId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryScheduleListByTaskIdAndNodeId(Long taskId,Long nodeId) {
		final String sql = "SELECT T.SCHEDULE_ID, T.TASKID,T.TASK_ID,T.TASK_NODE,T.FIN_RATE,T.EXPLAIN_,T.USER_ID,T.USER_NAME,T.WORKLOAD,T.WORKINGSTAGE,TO_CHAR(T.END_TIME, 'yyyy-mm-dd') AS END_TIME,T.WORKINGSTAGENAME  FROM T_TASK_SCHEDULE T WHERE T.TASKID =? AND T.TASK_NODE =? ORDER BY SCHEDULE_ID ASC";
		Query query = getSession().createSQLQuery(sql);
		query.setParameter(0, taskId);
		query.setParameter(1, nodeId);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}
	
	public JbpmTask findJbpmTaskByTaskId(String taskId) {
		Criteria criteria = createCriteria();
		criteria.add(Restrictions.eq("dbId", taskId));
		return (JbpmTask) criteria.uniqueResult();
	}
	
	public JbpmTask findJbpmTaskByInstanceId(String InstanceId) {
		Criteria criteria = createCriteria();
		criteria.add(Restrictions.eq("instanceId", InstanceId));
		return (JbpmTask) criteria.uniqueResult();
	}
	
	
	// 申请列表(关联业务)
	public EasyuiPage<Map<String, Object>> queryAppliedList(
			EasyuiPage<Map<String, Object>> page, Integer userId,
			String userName, String status, String eventTable,
			String eventTablesql, String eventTableRelevance,
			String formTypeId, String eventTableSelectsql, String attentionType) {

		StringBuffer sql = new StringBuffer();
		@SuppressWarnings("unused")
		List<Object> objList = new ArrayList<Object>();

		sql.append("SELECT distinct (T2.INSTANCE_ID) AS WF_INSTANCE_ID,T2.FORM_TYPE_ID AS WF_FORM_TYPE_ID, T5.REMIND_DATE AS WF_REMIND_DATE, TO_CHAR(T.CREATE_, 'yyyy-mm-dd') AS WF_CREATE_,");
		sql.append(" T2.STATUS AS WF_STATUS, T2.PROC_NAME AS WF_PROC_NAME, T2.FORM_ID AS WF_FORM_ID, T2.USER_NAME AS WF_USER_NAME, T2.ORG_NAME AS WF_ORG_NAME, T5.REMIND_STATUS AS WF_REMIND_STATUS, ");
		sql.append("TO_CHAR(T2.START_TIME, 'yyyy-mm-dd HH24:mi:ss') AS WF_START_TIME_,T2.WORKFLOW_ID AS WF_WORKFLOW_ID ,T2.TIME_LIMIT_STATUS AS WF_TIME_LIMIT_STATUS");
		sql.append(" , T2.USER_NAME || '(' || T2.ORG_NAME || ')' AS WF_USER_ORG ,T9.WF_ATTENTION_STATUS,T2.CUR_NODE AS WF_ACTIVITY_NAME_,T2.CUR_ORG AS WF_ORGNAME,T2.CUR_USER AS WF_USERNAME ");
		if (eventTable != null) {
			if (eventTablesql != null) {
				sql.append(" , ");
				sql.append(eventTablesql);
			}
		}

		sql.append(" FROM ");

		if (eventTable != null) {
			sql.append(eventTable).append(" E ");
			sql.append(" LEFT JOIN ");
		}

		sql.append(" WF_PROCESS_INSTANCE T2 ")

		.append(" ON ").append(eventTableRelevance).append(" = T2.FORM_ID ")
				.append(" AND T2.FORM_TYPE_ID =" + formTypeId);

		sql.append(" LEFT JOIN (")
				.append(" SELECT DISTINCT T6.PROCINST_, MAX(T6.CREATE_) AS CREATE_, MAX(DBID_) AS DBID_ ")
				.append(" FROM  JBPM4_TASK T6 ").append(" GROUP BY T6.PROCINST_) T ")
				.append(" ON T.PROCINST_ = T2.INSTANCE_ID ");

		sql.append(" LEFT JOIN (")
				.append("SELECT DISTINCT (T4.INSTANCE_ID), TO_CHAR(MAX(T4.REMIND_DATE), 'yyyy-mm-dd HH24:mi:ss') AS REMIND_DATE, MAX(T4.STATUS) AS REMIND_STATUS ")
				.append(" FROM ").append(" WF_REMIND T4 ")
				.append(" GROUP BY T4.INSTANCE_ID) T5 ")
				.append(" ON T2.INSTANCE_ID = T5.INSTANCE_ID");

		sql.append(" LEFT JOIN (")
				.append("SELECT T8.FOREIGN_ID, T8.STATUS AS WF_ATTENTION_STATUS FROM ")
				.append(" WF_ATTENTION T8  ")
				.append(" WHERE T8.ATTENTION_TYPE='2' ")
				.append(" AND T8.FORM_TYPE = '" + formTypeId + "' ")
				.append(" AND T8.USER_ID = " + userId + " ")
				.append(") T9 ON T9.FOREIGN_ID = ").append(eventTableRelevance);

		sql.append(" WHERE 1=1 ");

		if (status != null) {
			sql.append(" AND T2.STATUS ");
			if (status.equals("0")) {
				sql.append(" IS NULL ");
			} else {
				sql.append("='" + status + "' ");
			}
		} else {
			sql.append(" AND (T2.STATUS IS NULL OR T2.STATUS = '1') ");
		}

		if (userName != null) {
			userName = "'%" + userName + "%'";
			sql.append(" AND T2.USER_NAME LIKE " + userName + " ");
		}

		if (eventTableSelectsql != null) {
			sql.append(eventTableSelectsql);
		}

		if (attentionType != null) {
			if (attentionType.equals("1")) {
				sql.append(" AND T9.WF_ATTENTION_STATUS='1' ");
			} else if (attentionType.equals("0")) {
				sql.append(" AND T9.WF_ATTENTION_STATUS IS NULL ");
			}
		}

		sql.append(" ORDER BY T5.REMIND_DATE, WF_START_TIME_ DESC ");
		// TODO :
//		findBySql(page, sql.toString(), objList.toArray());

		return page;
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> queryDemandJbpmTaskByTaskName(String taskName) {
		StringBuffer sql = new StringBuffer()
			.append(" SELECT D.DEMAND_ID,D.TITLE,D.STATUS,D.PHASE,I.INSTANCE_ID, ")
			.append(" I.CUR_NODE,I.CUR_USER,I.STATUS,T.ACTIVITY_NAME_,T.DBID_, ")
			.append(" TO_CHAR(T.CREATE_, 'YYYY-MM-DD HH24:MI:SS') AS CREATE_TIME ")
			.append(" FROM TK_DEMAND D ")
			.append(" INNER JOIN WF_PROCESS_INSTANCE I ")
			.append(" ON D.DEMAND_ID = I.FORM_ID ")
			.append(" INNER JOIN JBPM4_TASK T ")
			.append(" ON T.PROCINST_ = I.INSTANCE_ID ")
			.append(" WHERE D.STATUS = '1' ")
			.append(" AND I.STATUS = '1' ")
			.append(" AND T.ACTIVITY_NAME_ =:taskName ");
		Query query = getSession().createSQLQuery(sql.toString());
		query.setString("taskName", taskName);
//		query.setParameter(0, taskName);
		return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<JbpmTask> getJbpmTasks(String instanceId) {
		Criteria criteria = createCriteria();
		criteria.add(Restrictions.eq("procinst", Long.parseLong(instanceId)));
		return criteria.list();
	}

}
